import logging
from datetime import date
from typing import Union, Optional

from fastapi import APIRouter, Query, Depends
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

good_info_router = APIRouter(prefix="/materialInfo", tags=["耗材接口"], dependencies=[Depends(oauth2_scheme)])


class GoodSearch(PaginationBase):
    fsiteNo: str = Field(default=None, description="站点编号")
    fcasno: str = Field(default=None, description="耗材编号")
    fclassstno: str = Field(default=None, description="耗材分类编号")


@good_info_router.get("/list", summary="查询耗材信息")
def get_good_info(good: GoodSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.m_ts_material_info where 1=1"
    if good.fsiteNo is not None:
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = good.fsiteNo
    if good.fcasno is not None:
        sql = sql + " and fmaterialname like concat('%',:fcasno ,'%') "
        param["fcasno"] = good.fcasno
    if good.fclassstno is not None:
        sql = sql + " and fclassstno = :fclassstno "
        param["fclassstno"] = good.fclassstno
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fsite_no,fmaterialno,fmaterialname,fbarcode,fengname,fclassstno,fspec,funit,feffday,fbuyprice,"
    sql = sql + " fsafeqty,fheightqty,fordunit,fordpack,fsupno,fmaterialstate,fdanger,fupddate,foperator "
    sql = sql + " from lims.m_ts_material_info where 1=1"
    if good.fsiteNo is not None:
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = good.fsiteNo
    if good.fcasno is not None:
        sql = sql + " and fmaterialname like concat('%',:fcasno ,'%') "
        param["fcasno"] = good.fcasno
    if good.fclassstno is not None:
        sql = sql + " and fclassstno = :fclassstno "
        param["fclassstno"] = good.fclassstno
    sql = sql + " order by  fmaterialno desc "
    sql = sql + " limit :limit  offset :offset "
    param["limit"] = good.limit
    param["offset"] = good.offset

    data = db.execute(text(sql), param).fetchall()

    info_dict = {}
    info_dict['total'] = count
    info_dict['pages'] = (count + good.limit - 1) // good.limit
    info_dict['limit'] = good.limit
    info_dict['page'] = good.pageIndex

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fsiteno'] = data[key][0]
        d1['fmaterialno'] = data[key][1]
        d1['fmaterialname'] = data[key][2]
        d1['fbarcode'] = data[key][3]
        d1['fengname'] = data[key][4]
        d1['fclassstno'] = data[key][5]
        d1['fspec'] = data[key][6]
        d1['funit'] = data[key][7]
        d1['feffday'] = data[key][8]
        d1['fbuyprice'] = data[key][9]
        d1['fsafeqty'] = data[key][10]
        d1['fheightqty'] = data[key][11]
        d1['fordunit'] = data[key][12]
        d1['fordpack'] = data[key][13]
        d1['fsupno'] = data[key][14]
        d1['fmaterialstate'] = data[key][15]
        d1['fdanger'] = data[key][16]
        d1['fupddate'] = data[key][17]
        d1['foperator'] = data[key][18]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": good.limit, "page": good.pageIndex,
                 "pages": (count + good.limit - 1) // good.limit,
                 "total": count}
    return BaseResponse.success(info_dict)


class ConsumableCreate(BaseModel):
    """耗材新增接口的数据模型"""
    fbarcode: str = Field(default="", description="条形码")
    fbuyprice: Union[str, float] = Field(default="0", description="购买价格")
    feffday: Optional[date] = Field(default=None, description="有效期")
    fengname: str = Field(default="", description="英文名称")
    fheightqty: Union[str, int] = Field(default="1", description="高位数量")
    fordpack: Union[str, int] = Field(default="1", description="包装规格")
    fordunit: str = Field(default="", description="包装单位")
    fsafeqty: Union[str, float] = Field(default="0", description="安全库存")
    fdanger: Union[str, int] = Field(default="0", description="危险等级")
    fgoodstate: Optional[str] = Field(default="", description="物品状态")
    fclassstno: Optional[str] = Field(default="", description="分类编号")
    fmaterialname: Optional[str] = Field(default="", description="耗材名称")
    fmaterialno: Optional[str] = Field(default="", description="耗材编号")
    fmaterialstate: Union[str, int] = Field(default="2", description="耗材状态")
    fsiteNo: Optional[str] = Field(default="", description="站点编号")
    fspec: str = Field(default="", description="规格")
    fstorageflag: str = Field(description="存储标志")
    funit: str = Field(description="单位")


@good_info_router.post("/create", summary="新增耗材信息")
def create_good_info(consumable: ConsumableCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        with db.begin():
            sql = "select fmaterialno from lims.m_ts_material_info where fmaterialno= :fmaterialno and fsite_No= :fsiteNo "
            data = db.execute(text(sql), {
                "fmaterialno": consumable.fmaterialno,
                "fsiteNo": consumable.fsiteNo
            }).fetchall()
            if len(data) > 0:
                return BaseResponse.error(message="耗材编号已存在")
            user_id = EncryptionMiddleware.decrypt_token(token)

            sql = "insert into lims.m_ts_material_info(fsite_no,fmaterialno,fmaterialname,fbarcode,fengname,fclassstno,fspec,funit,feffday,fbuyprice ,fsafeqty,fheightqty,fordunit,fordpack,fmaterialstate ,fdanger,fupddate,foperator)"
            sql = sql + (
                " values( :fsiteNo , :fmaterialno , :fmaterialname , :fbarcode , :fengname , :fclassstno , :fspec , :funit ,"
                " :feffday , :fbuyprice ,:fsafeqty, :fheightqty , :fordunit , :fordpack , :fmaterialstate , :fdanger ,now(), :userid );")
            i = db.execute(text(sql), {
                "fsiteNo": consumable.fsiteNo,
                "fmaterialno": consumable.fmaterialno,
                "fmaterialname": consumable.fmaterialname,
                "fbarcode": consumable.fbarcode,
                "fengname": consumable.fengname,
                "fclassstno": consumable.fclassstno,
                "fspec": consumable.fspec,
                "funit": consumable.funit,
                "feffday": consumable.feffday,
                "fbuyprice": consumable.fbuyprice,
                "fsafeqty": consumable.fsafeqty,
                "fheightqty": consumable.fheightqty,
                "fordunit": consumable.fordunit,
                "fordpack": consumable.fordpack,
                "fmaterialstate": consumable.fmaterialstate,
                "fdanger": consumable.fdanger,
                "userid": user_id
            })
            d1 = {}
            d1['fsiteNo'] = consumable.fsiteNo
            d1['fmaterialno'] = consumable.fmaterialno
            d1['fmaterialname'] = consumable.fmaterialname
            d1['fbarcode'] = consumable.fbarcode
            d1['fengname'] = consumable.fengname
            d1['fclassstno'] = consumable.fclassstno
            d1['fspec'] = consumable.fspec
            d1['funit'] = consumable.funit
            d1['feffday'] = consumable.feffday
            d1['fbuyprice'] = consumable.fbuyprice
            d1['fsafeqty'] = consumable.fsafeqty
            d1['fheightqty'] = consumable.fheightqty
            d1['fordunit'] = consumable.fordunit
            d1['fordpack'] = consumable.fordpack
            d1['fsupno'] = None
            d1['fmaterialstate'] = consumable.fmaterialstate
            d1['fdanger'] = consumable.fdanger
            d1['fupddate'] = None
            d1['foperator'] = user_id
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@good_info_router.post("/update", summary="修改耗材信息")
def update_good_info(consumable: ConsumableCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        with db.begin():
            user_id = EncryptionMiddleware.decrypt_token(token)
            sql = "update lims.m_ts_material_info"
            sql = sql + " set fmaterialname= :fmaterialname ,fbarcode= :fbarcode "
            sql = sql + ",fclassstno= :fclassstno ,fspec= :fspec "
            sql = sql + ",fsafeqty= :fsafeqty ,fordunit= :fordunit "
            sql = sql + ",fordpack= :fordpack ,fheightqty= :fheightqty "
            sql = sql + ",fengname= :fengname ,fbuyprice= :fbuyprice "
            sql = sql + ",fdanger= :fdanger ,fmaterialstate= :fmaterialstate"
            sql = sql + ",funit= :funit ,fupddate=now(),foperator= :userid "
            sql = sql + " where fmaterialno= :fmaterialno  and fsite_no= :fsiteNo "
            db.execute(text(sql), {
                "fmaterialname": consumable.fmaterialname,
                "fbarcode": consumable.fbarcode,
                "fclassstno": consumable.fclassstno,
                "fspec": consumable.fspec,
                "fsafeqty": consumable.fsafeqty,
                "fordunit": consumable.fordunit,
                "fordpack": consumable.fordpack,
                "fheightqty": consumable.fheightqty,
                "fengname": consumable.fengname,
                "fbuyprice": consumable.fbuyprice,
                "fdanger": consumable.fdanger,
                "fmaterialstate": consumable.fmaterialstate,
                "funit": consumable.funit,
                "userid": user_id,
                "fmaterialno": consumable.fmaterialno,
                "fsiteNo": consumable.fsiteNo
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class GoodDelete(BaseModel):
    fsiteNo: str = Field(description="站点编号")
    fmaterialno: str = Field(description="耗材编号")


@good_info_router.post("/delete", summary="删除耗材信息")
def delete_good_info(good: GoodDelete, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select sum(f1) "
            sql = sql + " from ( "
            sql = sql + " select count(1) f1 from lims.m_tc_good_inv where fmaterialno= :fmaterialno "
            sql = sql + " union"
            sql = sql + " select count(1) f1 from lims.m_tc_rec_detail where fmaterialno= :fmaterialno ) a"
            sql = sql + " where 1=1"
            row = db.execute(text(sql), {"fmaterialno": good.fmaterialno}).scalar()
            if row > 0:
                return BaseResponse.error(message="该资料在使用，不能删除")
            sql = "delete from lims.m_ts_material_info where fmaterialno= :fmaterialno and fsite_no= :fsiteNo "
            db.execute(text(sql), {"fmaterialno": good.fmaterialno, "fsiteNo": good.fsiteNo})
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
